const express = require("express");
const { parseConnectionUrl } = require("nodemailer/lib/shared");
const db = require("../../utils/db");
const router = express.Router();
// 未就诊数据渲染与查询
router.post("/RegisteredData", (req, res) => {
  const { reg_time, u_name, s_name, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 0 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%'
LIMIT ${(page - 1) * paginalNumber},${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 0 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 已就诊数据渲染与查询
router.post("/RegisteredData2", (req, res) => {
  const { reg_time, u_name, s_name, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 1 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%'
LIMIT ${(page - 1) * paginalNumber},${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 1 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 已退费数据渲染与查询
router.post("/RegisteredData3", (req, res) => {
  const { reg_time, u_name, s_name, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 2 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%'
LIMIT ${(page - 1) * paginalNumber},${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM register_record  
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE reg_status = 2 AND reg_time LIKE '%${reg_time}%' 
  AND u_name LIKE '%${u_name}%'
  AND s_name LIKE '%${s_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 下拉框
router.post("/select", (req, res) => {
  let sql = `  SELECT u_name FROM doctor`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 就诊状态
router.post("/seeDoctorStatus", (req, res) => {
  const { reg_id } = req.body;
  let sql = `UPDATE register_record SET reg_status=1 WHERE reg_id=${reg_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 退号状态
router.post("/exitStatus", (req, res) => {
  const { reg_id } = req.body;
  let sql = `UPDATE register_record SET reg_status=2 WHERE reg_id=${reg_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
//患者婚姻情况统计
router.post("/marryCount", (req, res) => {
  let sql = `SELECT (SELECT COUNT(1) AS num FROM patient WHERE s_marry=0) AS marry, 
  (SELECT COUNT(1) AS num FROM patient WHERE s_marry=1) AS unmarry,
  (SELECT COUNT(*) AS num FROM patient) AS total`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 新增挂号
router.post("/RegisteredAdd", (req, res) => {
  const {
    s_name,
    s_age,
    s_sex,
    s_tel,
    s_createTime,
    s_u_id,
    s_cardID,
    s_address,
    reg_price,
    reg_type,
  } = req.body;
  let sql = `INSERT INTO patient 
  VALUES(NULL,'${s_name}',${s_sex},${s_age},${s_tel},'${s_createTime}',${s_u_id},${s_cardID},
  DEFAULT,'${s_address}',DEFAULT,DEFAULT,'无','无') `;
  let sql2 = `INSERT INTO register_record VALUES(NULL,(SELECT @@IDENTITY),'${s_createTime}',${reg_price},
  DEFAULT,DEFAULT,${s_u_id},${reg_type},DEFAULT,DEFAULT)`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data1: data, data2: data2, msg: "success" });
        }
      });
    }
  });
});
// 下拉框
router.post("/RegisteredSelect", (req, res) => {
  let sql = `SELECT u_name,u_id FROM doctor`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 新增挂号中的收费
router.post("/pay", (req, res) => {
  const { a_time, a_price, a_u_id, a_re_id, a_m_id, a_s_id, a_reg_id } =
    req.body;
  let sql = `INSERT INTO expense_record VALUES(NULL,'${a_time}',1,DEFAULT,${a_price},DEFAULT,${a_u_id},${a_re_id},${a_m_id},${a_s_id},DEFAULT,${a_reg_id},DEFAULT)`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 挂号记录编辑
router.post("/RegisteredEdit", (req, res) => {
  const { s_id, s_age, s_sex, s_tel, s_cardID, s_address, reg_type, reg_id } =
    req.body;
  let sql = `UPDATE register_record SET reg_type=${reg_type} WHERE reg_id=${reg_id};`;
  let sql2 = `UPDATE patient SET s_age = ${s_age},s_sex=${s_sex},s_tel=${s_tel},s_cardID=${s_cardID},s_address='${s_address}' WHERE s_id =${s_id}`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data1: data, data2: data2, msg: "success" });
        }
      });
    }
  });
});
// 挂号记录修改
router.post("/doctorEdit", (req, res) => {
  const { reg_id } = req.body;
  let sql = `SELECT * FROM register_record JOIN doctor ON register_record.reg_user_id = doctor.u_id 
  JOIN patient ON register_record.reg_s_id = patient.s_id WHERE register_record.reg_id=${reg_id}`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 收费
router.post("/payCharge", (req, res) => {
  const { a_time, a_price, a_u_id, a_re_id, a_m_id, a_s_id, a_count } =
    req.body;
  let sql = `INSERT INTO expense_record VALUES(NULL,'${a_time}',1,1,${a_price},0,${a_u_id},${a_re_id},${a_m_id},${a_s_id},${a_count},DEFAULT,DEFAULT)`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 收费信息查询
router.post("/lookCharge", (req, res) => {
  const { reg_id } =req.body;
  let sql = `SELECT * FROM register_record  
  JOIN doctor ON register_record.reg_user_id = doctor.u_id 
  JOIN  patient ON register_record.reg_s_id = patient.s_id
  WHERE register_record.reg_id=${reg_id}`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 退号
router.post("/delData", (req, res) => {
  const { reg_id } = req.body;
  let sql = `DELETE FROM register_record WHERE reg_id = ${reg_id}`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
module.exports = router;
